package de.unistuttgart.iaas.interaction.skype.database;

import java.sql.*;
import de.unistuttgart.iaas.interaction.skype.util.ConfigFileHandler;


/** 
 * This class creates the connection to database and closes it.
 * @author Lina Sun
 *
 */
public class MySQLAccess {
	
	private Connection connect= null;
	private Statement statement = null;
	private PreparedStatement preparedStatement = null;
	private ResultSet resultSet = null;

	
	/**
	 * This method connects the database skydb and creates five tables if these tables don't exist.
	 * 1.table--mailregister
	 * 2.table--mailbox
	 * 3.table--sentmails
	 * 4.table--mailattachments
	 * 
	 * @param config the configFileHandler
	 * @return Connection connect
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 * 
	 */
	public Connection connDataBase(ConfigFileHandler config) throws Exception{
			
		String db_username = config.getSkydb_username();
		String db_password = config.getSkydb_password();
		String db_url = config.getSkydb_url();
		String table_register = config.getSkydb_table_register();
		String table_sentFileMessage = config.getSkydb_table_sentFileMessage();
		String table_sentTextMessage= config.getSkydb_table_sentTextMessage();
		String table_recievedFileMessage = config.getSkydb_table_recievedFileMessage();
		String table_recievedTextMessage = config.getSkydb_table_recievedTextMessage();

		

		try{
			//load the MySQL drive
			Class.forName("com.mysql.jdbc.Driver");		
			connect = DriverManager.getConnection(db_url, db_username, db_password);
		
			String table1Sql = "create table if not exists skydb."+table_sentTextMessage +"("
			+ "id varchar(45) not null primary key, to_username varchar(45), from_username varchar(45), textMessage longtext, sentDate datetime not null,"
			+ "reply varchar(45) not null, responsePattern longtext, timeout datetime, state varchar(45) not null);";
		
			String table2Sql = "create table if not exists skydb."+ table_sentFileMessage +"("
		     + "id varchar(45) not null primary key, to_username varchar(45) not null, from_username varchar(45) not null, fileMessage longblob not null,"
		     + "sentDate datetime not null, reply varchar(45) not null, responsePattern longtext, "
		     + "timeout datetime, state varchar(45));";
			
			String table3Sql = "create table if not exists skydb."+ table_recievedFileMessage + "(id varchar(45) not null primary key,"
		     + "requestId varchar(45) not null, from_username varchar(45), fileMessage longblob, filename varchar(45) not null, " +
		     "recievedDate datetime, class varchar(45) not null, state varchar(45) not null);";
			
			String table4Sql = "create table if not exists skydb."+ table_recievedTextMessage +"(id varchar(45) not null primary key,"
			+ "requestId varchar(45), from_username varchar(45), textMessage longtext, recievedDate datetime, class varchar(45), state varchar(45));";
			
			String table5Sql = "create table if not exists skydb."+ table_register +"(idregister int not null primary key auto_increment,"
			+ "skype_username varchar(45) not null, name varchar(45) not null);";
		
			if(connect!=null){
				statement = connect.createStatement();
				statement.executeUpdate(table1Sql);
				statement.executeUpdate(table2Sql);
				statement.executeUpdate(table3Sql);
				statement.executeUpdate(table4Sql);
				statement.executeUpdate(table5Sql);

			}else{
				
				System.out.println("No database.");
				
			}
		
			}catch(ClassNotFoundException e){
				
				System.out.println("Database driver has been not found.");
			}
			catch (SQLException e1) {
			    // TODO Auto-generated catch block
				
				//ErrorHandler
				System.out.println("Failed to connect database.");

			   }

		return connect;

	}

	// You need to close the resultSet
	public void close() {
		try {
			if (resultSet != null) {
				resultSet.close();
			}

			if (statement != null) {
				statement.close();
			}

			if (connect != null) {
				connect.close();
			}
		} catch (Exception e) {

		}
	}
	


}
